package com.softwinner.bt.util;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.softwinner.bt.BtApplication;
import com.softwinner.bt.bean.BtContact;
import com.softwinner.bt.bean.Record;
import com.softwinner.bt.service.BtService;
import com.softwinner.bt.R;
import android.R.integer;
import android.content.ContentProviderOperation;
import android.content.ContentProviderResult;
import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;
import android.content.OperationApplicationException;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.os.RemoteException;
import android.provider.ContactsContract;
import android.provider.ContactsContract.RawContacts;
import android.provider.ContactsContract.CommonDataKinds.Email;
import android.provider.ContactsContract.CommonDataKinds.Phone;
import android.provider.ContactsContract.CommonDataKinds.StructuredName;
import android.provider.ContactsContract.RawContacts.Data;
import android.util.Log;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;
public class DatabaseHelper extends SQLiteOpenHelper {
    private static DatabaseHelper mDatabaseHelper = null;
    public static final String DB_NAME = "phonebook.db";
    public static final String BOOK_TABLE_NAME = "phonebook";
    public static final String RECORD_TABLE_NAME = "phone_record";
    public static final String ID = "_id";
    public static final String BT_ADD = "_address";
    public static final String NAME = "phonename";
    public static final String NUMBER = "phonenumber";
    public static final String ADDRESS="address";
    public static final String TIME="time";
    private static final int VERSION = 1;
    public static final String RECORD_TYPE = "type";
    
    public static final int TYPE_CALL = 1;
    public static final int TYPE_COMING = 2;
    public static final int TYPE_DIS = 3;
    Context context;
    private List<BtContact> list=new ArrayList<BtContact>();
    private DatabaseHelper(Context context) {
        super(context, DB_NAME, null, VERSION);
        this.context=context; 
    }

    public static DatabaseHelper getDatabaseHelper(Context context) {
        if (mDatabaseHelper == null) {
            mDatabaseHelper = new DatabaseHelper(context);
        }
        return mDatabaseHelper;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE IF NOT EXISTS " + BOOK_TABLE_NAME + "(" 
        		+ ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
        		+ BT_ADD + " text,"
        		+ NAME + " text,"	
                + NUMBER + " text)";
        db.execSQL(sql);
        String sql2 = "CREATE TABLE IF NOT EXISTS " + RECORD_TABLE_NAME + "("
                + ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + BT_ADD + " text,"
        		+ NAME + " text,"
                + NUMBER + " text,"
                + ADDRESS + " text,"
                + TIME + " text,"
        		+ RECORD_TYPE + " INTEGER)";
        db.execSQL(sql2);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

     public ArrayList<BtContact> queryAllContacts() {
        ArrayList<BtContact> contacts = new ArrayList<BtContact>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(BOOK_TABLE_NAME, null, BT_ADD+"=?", new String[]{BtService.mBtPairAdd}, null, null,
                "_id asc");
        if (cursor.getCount() > 0) {
            while (cursor.moveToNext()) {
                String name = cursor.getString(cursor.getColumnIndex(NAME));
                String number = cursor.getString(cursor.getColumnIndex(NUMBER));
                BtContact contact = new BtContact();
                contact.setName(name);
                contact.setNumber(number);
                contacts.add(contact);
            }
        }
        cursor.close();
        return contacts;
    }
     public void saveContact(String name,String phoneNumber){
         ArrayList<ContentProviderOperation> ops = new ArrayList<ContentProviderOperation>();
         int rawContactInsertIndex = ops.size();
         ops.add(ContentProviderOperation.newInsert(RawContacts.CONTENT_URI)
                 .withValue(RawContacts.ACCOUNT_TYPE, null)
                 .withValue(RawContacts.ACCOUNT_NAME, null)
                 .build());
         ops.add(ContentProviderOperation.newInsert(android.provider.ContactsContract.Data.CONTENT_URI)
                 .withValueBackReference(Data.RAW_CONTACT_ID, rawContactInsertIndex)
                 .withValue(Data.MIMETYPE, StructuredName.CONTENT_ITEM_TYPE)
                 .withValue(StructuredName.GIVEN_NAME, name)
                 .build());
         ops.add(ContentProviderOperation.newInsert(android.provider.ContactsContract.Data.CONTENT_URI)
                  .withValueBackReference(Data.RAW_CONTACT_ID, rawContactInsertIndex)
                  .withValue(Data.MIMETYPE, Phone.CONTENT_ITEM_TYPE)
                  .withValue(Phone.NUMBER, phoneNumber)
                  .withValue(Phone.TYPE, Phone.TYPE_MOBILE)
                  .withValue(Phone.LABEL,R.string.contact_num_title)
                  .build());
        // values.put(RawContacts.AGGREGATION_MODE,RawContacts.AGGREGATION_MODE_DISABLED);
//         ops.add(ContentProviderOperation.newInsert(android.provider.ContactsContract.Data.CONTENT_URI)
//                  .withValueBackReference(Data.RAW_CONTACT_ID, rawContactInsertIndex)
//                  .withValue(Data.MIMETYPE, Email.CONTENT_ITEM_TYPE)
//                  .withValue(Email.DATA, "liming@itcast.cn")
//                  .withValue(Email.TYPE, Email.TYPE_WORK)
//                  .build());
		try {
			ContentProviderResult[] results = context.getContentResolver()
			     .applyBatch(ContactsContract.AUTHORITY, ops);
//			for(ContentProviderResult result : results){
//	             Log.i("zhu","result.uri=========="+result.uri.toString());
//	         }
			//return results.length;
		} catch (RemoteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			//return -1;
		} catch (OperationApplicationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			//return -1;
		}
     }
    public ArrayList<Record> queryAllRecords() {
        ArrayList<Record> records = new ArrayList<Record>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(RECORD_TABLE_NAME, null,BT_ADD+"=?", new String[]{BtService.mBtPairAdd}, null,
                null, "_id desc");
        if (cursor.getCount() > 0) {
            while (cursor.moveToNext()) {
                int id = cursor.getInt(cursor.getColumnIndex(ID));
                String name = cursor.getString(cursor.getColumnIndex(NAME));
                String number = cursor.getString(cursor.getColumnIndex(NUMBER));
                String address=cursor.getString(cursor.getColumnIndex(ADDRESS));
                String time=cursor.getString(cursor.getColumnIndex(TIME));
                int type = cursor.getInt(cursor.getColumnIndex(RECORD_TYPE));
                Record record = new Record();
                record.setId(id);
                record.setName(name);
                record.setNumber(number);
                record.setAddress(address);
                record.setTime(time);
                record.setType(type);
                records.add(record);
            }
        }
        cursor.close();
        return records;
    }

    public long insertContact(String command) {
        SQLiteDatabase db = getWritableDatabase();
        int type;
        int nameLen;
        int numLen;
        String name;
        String num;
        type = Integer.parseInt(command.substring(2, 3));
        if (type != 0 && type != 1) {
            return -1;
        }
        nameLen = Integer.parseInt(command.substring(3, 5));
        numLen = Integer.parseInt(command.substring(5, 7));
        
        if(command.length() > numLen+6){
        	num = command.substring(command.length() - numLen);
    		name= command.substring(7,command.length() - numLen);
    	}else{
    		name = command.substring(7, 7 + nameLen / 3);
            num = command.substring(7 + nameLen / 3, command.length());
    	}        
        if (num.length()==0 || isNumberExist(num)) {
            return -1;
        }
        ContentValues contentValues = new ContentValues();
        contentValues.put(BT_ADD, BtService.mBtPairAdd);
        contentValues.put(NAME, name);
        contentValues.put(NUMBER, num);
        long result=db.insert(BOOK_TABLE_NAME, null, contentValues);
        if(result!=-1){
        Log.i("zhu", "insert name===="+name+",,,num==="+num);
	        BtContact contact=new BtContact();
	        contact.setName(name);
	        contact.setNumber(num);
	        list.add(contact);
        }else{
        	list.clear();
        }
        //contentValues.put(RawContacts.AGGREGATION_MODE,RawContacts.AGGREGATION_MODE_DISABLED);
        return result;
    }
    /** 
     * 批量添加通讯录 
     *  
     * @throws OperationApplicationException 
     * @throws RemoteException 
     */  
    public void BatchAddContact(List<BtContact> list)  
            throws RemoteException, OperationApplicationException {  
        //GlobalConstants.PrintLog_D("[GlobalVariables->]BatchAddContact begin");  
        ArrayList<ContentProviderOperation> ops = new ArrayList<ContentProviderOperation>();  
        int rawContactInsertIndex = 0;  
        for (BtContact contact : list) {  
            rawContactInsertIndex = ops.size(); // 有了它才能给真正的实现批量添加  
  
            ops.add(ContentProviderOperation.newInsert(RawContacts.CONTENT_URI)  
                    .withValue(RawContacts.ACCOUNT_TYPE, null)  
                    .withValue(RawContacts.ACCOUNT_NAME, null)  
                    .withYieldAllowed(true).build());  
  
            // 添加姓名  
            ops.add(ContentProviderOperation  
                    .newInsert(  
                            android.provider.ContactsContract.Data.CONTENT_URI)  
                    .withValueBackReference(Data.RAW_CONTACT_ID,  
                            rawContactInsertIndex)  
                    .withValue(Data.MIMETYPE, StructuredName.CONTENT_ITEM_TYPE)  
                    .withValue(StructuredName.DISPLAY_NAME, contact.getName())  
                    .withYieldAllowed(true).build());  
            // 添加号码  
            ops.add(ContentProviderOperation  
                    .newInsert(  
                            android.provider.ContactsContract.Data.CONTENT_URI)  
                    .withValueBackReference(Data.RAW_CONTACT_ID,  
                            rawContactInsertIndex)  
                    .withValue(Data.MIMETYPE, Phone.CONTENT_ITEM_TYPE)  
                    .withValue(Phone.NUMBER, contact.getNumber())  
                    .withValue(Phone.TYPE, Phone.TYPE_MOBILE)  
                    .withValue(Phone.LABEL, "").withYieldAllowed(true).build());  
        }  
        if (ops != null) {
        	ContentValues contentValues = new ContentValues();
        	contentValues.put(RawContacts.AGGREGATION_MODE,RawContacts.AGGREGATION_MODE_DISABLED);
            // 真正添加  
            ContentProviderResult[] results = context.getContentResolver()  
                    .applyBatch(ContactsContract.AUTHORITY, ops);  
            // for (ContentProviderResult result : results) {  
            // GlobalConstants  
            // .PrintLog_D("[GlobalVariables->]BatchAddContact "  
            // + result.uri.toString());  
            // }  
        }  
    }  
    public void insertSystemContact(String command) {
        SQLiteDatabase db = getWritableDatabase();
        int type;
        int nameLen;
        int numLen;
        String name;
        String num;
        type = Integer.parseInt(command.substring(2, 3));
        if (type != 0 && type != 1) {
            return ;//-1;
        }
        nameLen = Integer.parseInt(command.substring(3, 5));
        numLen = Integer.parseInt(command.substring(5, 7));
        
        if(command.length() > numLen+6){
        	num = command.substring(command.length() - numLen);
    		name= command.substring(7,command.length() - numLen);
    	}else{
    		name = command.substring(7, 7 + nameLen / 3);
            num = command.substring(7 + nameLen / 3, command.length());
    	}        
        if (num.length()==0 || isSystemNumberExist(num)) {
        	//Log.i("zhu", "return====isSystemNumberExist(num)====="+num);
            return;//-1;
        }
//        ContentValues contentValues = new ContentValues();
//        //contentValues.put(BT_ADD, BtService.mBtPairAdd);
//        contentValues.put(NAME, StringFilter(name));
//        contentValues.put(NUMBER, num);
//        contentValues.put(RawContacts.AGGREGATION_MODE,RawContacts.AGGREGATION_MODE_DISABLED);
        Log.i("zhu", "insert name===="+name+",,,num==="+num);
        BtContact contact=new BtContact();
        contact.setName(name);
        contact.setNumber(num);
        list.add(contact);
        //saveContact(name,num);
    }
    public void insertPhoneList(){
	String[] names=context.getResources().getStringArray(R.array.def_contacts_names);
	String[] numbers=context.getResources().getStringArray(R.array.def_contacts_numbers);
	for(int i=0;i<numbers.length;i++){
		//ContentValues contentValues = new ContentValues();
        	// //contentValues.put(BT_ADD, BtService.mBtPairAdd);
        	//contentValues.put(NAME, StringFilter(names[i]));
        	//contentValues.put(NUMBER,numbers[i]);
        	//contentValues.put(RawContacts.AGGREGATION_MODE,RawContacts.AGGREGATION_MODE_DISABLED);
        	//Log.i("zhu", "insert name===="+names[i]+",,,num==="+numbers[i]);
        	//saveContact(names[i],numbers[i]); 
		BtContact contact=new BtContact();
        contact.setName(names[i]);
        contact.setNumber(numbers[i]);
        list.add(contact);
	}
    }
    public List<BtContact> getSystemContacts(){
    	return list;
    }
    public String StringFilter(String str) throws PatternSyntaxException {
		// 只允许字母和数字
		// String regEx = "[^a-zA-Z0-9]";
		// 清除掉所有特殊字符
		String regEx = "[`~!@#$%^&*()+=|{}':;',\\[\\].<>~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]";
		Pattern p = Pattern.compile(regEx);
		Matcher m = p.matcher(str);
		return m.replaceAll("").trim();
	}
    public boolean isNumberExist(String num){
    	SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(BOOK_TABLE_NAME, null,BT_ADD+"=? and " + NUMBER + "=?", new String[]{BtService.mBtPairAdd,num}, null,
                null, "_id desc");
        if(cursor.getCount()>0)	return true;
    	return false;
    }
    public boolean isSystemNumberExist(String num){
    	Uri uri =  ContactsContract.Contacts.CONTENT_URI;
        ContentResolver contentResolver = context.getContentResolver();
        Cursor cursor = contentResolver.query(uri, null, null, null, null);
        while(cursor.moveToNext()){
            StringBuilder sb = new StringBuilder();
            String contactId = cursor.getString(cursor.getColumnIndex(ContactsContract.Contacts._ID));  
            String name = cursor.getString(cursor.getColumnIndex(ContactsContract.Contacts.DISPLAY_NAME)); 
            sb.append("contactId=").append(contactId).append(",name=").append(name);
            
            Cursor phones = contentResolver.query(ContactsContract.CommonDataKinds.Phone.CONTENT_URI,  
                    null,  
                    ContactsContract.CommonDataKinds.Phone.CONTACT_ID +" = "+ contactId,  
                    null, null);  
            while(phones.moveToNext()){
                String phoneNumber = phones.getString(phones.getColumnIndex(
                        ContactsContract.CommonDataKinds.Phone.NUMBER));
                //Log.i("zhu", "phoneNumber========"+phoneNumber+",,,num========="+num);
                if(num.equals(phoneNumber))
                	return true;
                sb.append(",phone=").append(phoneNumber);
            }
            phones.close();
            
//            Cursor emails = contentResolver.query(ContactsContract.CommonDataKinds.Email.CONTENT_URI,  
//                       null,  
//                       ContactsContract.CommonDataKinds.Email.CONTACT_ID + " = " + contactId,  
//                       null, null);  
//            while (emails.moveToNext()) {  
//                String emailAddress = emails.getString(emails.getColumnIndex(
//                        ContactsContract.CommonDataKinds.Email.DATA));
//                sb.append(",emailAddress=").append(emailAddress);
//            } 
//            emails.close();
            //Log.i("zhu", sb.toString());
        }
        cursor.close();
    	return false;
    }
    public String getNameByNum(String num) throws Exception{
    	String name="";
    	SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(BOOK_TABLE_NAME, null,BT_ADD+"=? and " + NUMBER + "=?", new String[]{BtService.mBtPairAdd,num}, null,
                null, "_id desc");
        if(cursor.getCount()>0){
        	while (cursor.moveToNext()) {
        		name = cursor.getString(cursor.getColumnIndex(NAME));
            }
        }
    	return name;
    }
    public ArrayList<BtContact> fuzzyContactByNum(String num){
    	String name="";
    	ArrayList<BtContact> contacts=new ArrayList<BtContact>();
    	SQLiteDatabase db = getReadableDatabase();
    	Cursor cursor= db.query("phonebook", new String[]{NAME,NUMBER}, "phonenumber like ?", new String[]{"%"+num+"%"}, null, null, "_id desc", null);
        //Log.i("zhu","cursor.getCount()======="+cursor.getCount());
        if(cursor.getCount()>0){
        	while (cursor.moveToNext()) {
        		BtContact contact=new BtContact();
        		contact.setName(cursor.getString(cursor.getColumnIndex(NAME)));
        		contact.setNumber(cursor.getString(cursor.getColumnIndex(NUMBER)));
        		contacts.add(contact);
            }
        	 //Log.i("zhu","contacts.size()======="+contacts.size());
        }
    	return contacts;
    }
    public ArrayList<Record> fuzzyRecordByNum(String num){
    	ArrayList<Record> records=new ArrayList<Record>();
    	SQLiteDatabase db = getReadableDatabase();
    	Cursor cursor= db.query("phone_record", new String[]{NAME,NUMBER,ADDRESS,TIME}, "phonenumber like ?", new String[]{"%"+num+"%"}, null, null, "_id desc", null);
        //Log.i("zhu","cursor.getCount()======="+cursor.getCount());
        if(cursor.getCount()>0){
        	while (cursor.moveToNext()) {
        		Record record=new Record();
        		record.setName(cursor.getString(cursor.getColumnIndex(NAME)));
        		record.setNumber(cursor.getString(cursor.getColumnIndex(NUMBER)));
        		record.setAddress(cursor.getString(cursor.getColumnIndex(ADDRESS)));
        		record.setTime(cursor.getString(cursor.getColumnIndex(TIME)));
        		records.add(record);
            }
        	 //Log.i("zhu","contacts.size()======="+contacts.size());
        }
    	return records;
    }
    public long insertRecord(Record record) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(BT_ADD, BtService.mBtPairAdd);
        contentValues.put(NAME, record.getName());
        contentValues.put(NUMBER, record.getNumber());
        contentValues.put(ADDRESS,record.getAddress());
        contentValues.put(TIME, record.getTime());
        contentValues.put(RECORD_TYPE, record.getType());
        return db.insert(RECORD_TABLE_NAME, null, contentValues);
    }

    public void deleNumber(String number) {
        SQLiteDatabase db = getWritableDatabase();
        db.delete(BOOK_TABLE_NAME, NUMBER+"=? and " + BT_ADD + "=?",new String[]{number,BtService.mBtPairAdd});
    }

    public void deleRecord(Record record) {
        SQLiteDatabase db = getWritableDatabase();
        db.delete(RECORD_TABLE_NAME, " " + ID + " = ? ",
                new String[] { record.getId() + "" });
    }
    
    public void delTopRecord(String num) {
    	SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(RECORD_TABLE_NAME, null,
        		BT_ADD+"=? and " + NUMBER + "=? and " + RECORD_TYPE + "=? ", 
        		new String[]{BtService.mBtPairAdd,num,TYPE_DIS+""}, null,
                null, "_id desc");
        if(cursor.getCount()>0){
        	cursor.moveToFirst();
        	int id = cursor.getInt(cursor.getColumnIndex(ID));
        	db.delete(RECORD_TABLE_NAME, " " + ID + " = ? ",
                    new String[] { id + "" });
        }
    }

    public void delAllNumber() {
        SQLiteDatabase db = getWritableDatabase();
        db.delete(BOOK_TABLE_NAME, ID+">=? and " + BT_ADD + "=?",new String[]{"0",BtService.mBtPairAdd});
    }

    public void delAllRecord() {
        SQLiteDatabase db = getWritableDatabase();
        db.delete(RECORD_TABLE_NAME, ID+">=? and " + BT_ADD + "=?",new String[]{"0",BtService.mBtPairAdd});
    }
}
